def data(s):
    nums = []  # 排名
    hots = []  # 热度
    names = []  # 名称

    for rows in s:
        nums.append(rows[0].value.strip())
        names.append(rows[1].value[0:2])
        hots.append(rows[2].value.strip())
    nums.pop(0)
    names.pop(0)
    hots.pop(0)

    # 制作图形(创建柱状图对象)
    bar = Bar()
    # 关联数据
    bar.add_xaxis(names)
    bar.add_yaxis("热度", hots)
    bar.add_yaxis("排名", nums)
    # 展示图形（html）
    return bar


# 电影排行榜，连续剧排行榜，动漫排行榜，综艺排行榜
import requests
from bs4 import BeautifulSoup
from pyecharts.charts import Line,Bar,Pie,Grid
from openpyxl import Workbook,load_workbook
from pyecharts import options

header = {
    "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/90.0.4430.212 Safari/537.36",
    "Cookie": "BIDUPSID=0F24FB149E086AC1577F145CD2283657; PSTM=1606195031; BAIDUID=0F24FB149E086AC103288BF8E74100D5:FG=1; BAIDUID_BFESS=0F24FB149E086AC103288BF8E74100D5:FG=1; BD_UPN=12314753; COOKIE_SESSION=15097934_1_1_1_0_0_0_0_1_0_0_0_0_0_0_2_0_1622514579_1622514577%7C2%230_1_1622514577%7C1; BD_HOME=1; H_PS_PSSID=34099_34004_33772_33855_33607_26350; delPer=0; BD_CK_SAM=1; PSINO=7; BDORZ=B490B5EBF6F3CD402E515D22BCDA1598; H_PS_645EC=81e3KY94y3MIlN7swY9OnPXpbDX4xwiknn7QrQVNYkCyCAQeroLrWnc0j%2F4; BA_HECTOR=2425a1ak0hag2100uc1gbliso0r; BDSVRTM=0"
}

# 封装数据对象
title = ["电影排行榜","连续剧排行榜","动漫排行榜","综艺排行榜"]  # 标题
nums = []  # 排名
hots = []  # 热度
names = []  # 名称

html = requests.get("http://w3ic.com/", headers=header)
soup = BeautifulSoup(html.text, "html.parser")
#爬标题的内容
for i in soup.select(".bang_1"):
    nums.append(i.contents[1].string)
    hots.append(i.contents[3].string.strip("℃"))
    names.append(i.contents[5].string)

#创建excel对象
wb = Workbook()
ws1 = wb.create_sheet(title[0],0)
ws2 = wb.create_sheet(title[1],1)
ws3 = wb.create_sheet(title[2],2)
ws4 = wb.create_sheet(title[3],3)

#加个头
ws1.append(["排名","名字","热度"])
ws2.append(["排名","名字","热度"])
ws3.append(["排名","名字","热度"])
ws4.append(["排名","名字","热度"])
#给每一个sheet加入数据
for i in range(len(nums)):
    if i>=0 and i<=11:
        ws1.append([nums[i],names[i],hots[i]])
    if i>=12 and i<=23:
        ws2.append([nums[i], names[i], hots[i]])
    if i>=24 and i<=35:
        ws3.append([nums[i], names[i], hots[i]])
    if i>=36 and i<=47:
        ws4.append([nums[i], names[i], hots[i]])
wb.save("结果.xlsx")

Lw = load_workbook("结果.xlsx")
sheet1 = Lw[title[0]]
sheet2 = Lw[title[1]]
sheet3 = Lw[title[2]]
sheet4 = Lw[title[3]]

#创建组合对象
grid = Grid()
#
grid.add(data(sheet1.iter_rows()),grid_opts=options.GridOpts(pos_top="60%", pos_left="60%"))
grid.add(data(sheet3.iter_rows()),grid_opts=options.GridOpts(pos_top="60%", pos_right="60%"))
grid.add(data(sheet2.iter_rows()),grid_opts=options.GridOpts(pos_bottom="60%", pos_left="60%"))
grid.add(data(sheet4.iter_rows()),grid_opts=options.GridOpts(pos_bottom="60%", pos_right="60%"))
grid.render("组合.html")

